﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace KLADR
{
    public class ImportKladr
    {
        private string dbConnectionString;
        private string sqlConnectionString;

        public string DbConnectionString 
        {
            get
            { 
                return dbConnectionString; 
            } 
            set
            { 
                dbConnectionString = value;
                dbConnection = new OleDbConnection(dbConnectionString);
                dbConnection.Open();
            } 
        }
        public string SqlConnectionString 
        {
            get 
            { 
                return sqlConnectionString;
            } 
            set 
            {
                sqlConnectionString = value;
                sqlConnection = new SqlConnection(sqlConnectionString);
                sqlConnection.Open();
            }
        }

        private OleDbConnection dbConnection;
        private SqlConnection sqlConnection;

        public void import()
        {
            importAltnames();
            importDoma();
            importFlat();
            importSocrbase();
            importStreet();
            importKladr();
        }

        private void importProc(string table, string parameters)
        {
            string convertedString;
            string selectCommand = string.Format("SELECT {0} FROM {1}", parameters, table);
            string deleteCommand = string.Format("DELETE FROM kladr.{0}",table);
            string[] lst_params = parameters.Split(new char[] { ',' });
            StringBuilder ins_params = new StringBuilder();
            StringBuilder ins_list = new StringBuilder();
            foreach (string prm in lst_params)
            {
                ins_params.Append(string.Format("@{0},",prm.Trim()));
                ins_list.Append(string.Format("[{0}],", prm.Trim()));
            }
            ins_params.Remove(ins_params.Length-1, 1);
            ins_list.Remove(ins_list.Length-1, 1);
            string insertCommand = string.Format("INSERT INTO kladr.{0} ({1}) VALUES ({2})",table, ins_list.ToString(), ins_params.ToString());
            OleDbCommand cmd = new OleDbCommand(selectCommand, dbConnection);
            cmd.CommandType = CommandType.Text;
            OleDbDataReader dr = cmd.ExecuteReader();
            SqlCommand sqlCmd = new SqlCommand(deleteCommand, sqlConnection);
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.ExecuteNonQuery();
            sqlCmd = new SqlCommand(insertCommand, sqlConnection);
            sqlCmd.CommandType = CommandType.Text;

            Encoding cp866 = Encoding.GetEncoding(866);
            Encoding cp1251 = Encoding.GetEncoding(1251);
            Encoding unicode = Encoding.Unicode;

            while (dr.Read())
            {
                sqlCmd.Parameters.Clear();
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    string value = dr[i].ToString();
                    convertedString = value;
                    byte[] cp1251bytes = Encoding.Convert(cp866, unicode, cp1251.GetBytes(value));
                    char[] cp1251chars = new char[unicode.GetCharCount(cp1251bytes, 0, cp1251bytes.Length)];
                    unicode.GetChars(cp1251bytes, 0, cp1251bytes.Length, cp1251chars, 0);
                    convertedString = new string(cp1251chars);

                    sqlCmd.Parameters.AddWithValue(string.Format("@{0}", dr.GetName(i)), convertedString);
                }
                sqlCmd.ExecuteNonQuery();
            }
        }

        private void importAltnames()
        {
            importProc("altnames", "oldcode, newcode, level");
        }
        private void importDoma()
        {
            importProc("doma","name,korp,socr,code,index,gninmb,uno,ocatd");
        }
        private void importFlat()
        {
            importProc("flat","name,code,index,gninmb,uno,np");
        }
        private void importKladr()
        {
            importProc("kladr","name,socr,code,index,gninmb,uno,ocatd,status");
        }
        private void importSocrbase()
        {
            importProc("socrbase","level,scname,socrname,kod_t_st");
        }
        private void importStreet()
        {
            importProc("street", "name,socr,code,index,gninmb,uno,ocatd");
        }
    }
}
